package com.apestech.api.query;

import com.apestech.api.exp.XLSTable;
import com.apestech.framework.db.SqlSession;
import com.apestech.framework.dictionary.modul.Table;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.util.DateUtil;
import com.apestech.framework.util.FileUtil;
import com.apestech.framework.util.StringUtil;
import com.apestech.framework.util.callback.SpiltStrCallback;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

public class XLSRead extends StringUtil {

    private JSONObject allmerged=new JSONObject();

    public class Merged{
        private String key;
        private int firstColumn;
        private int lastColumn;
        private int firstRow;
        private int lastRow;
        private Object value;

        public Merged(int firstRow,int lastRow,int firstColumn,int lastColumn){
            this.firstRow=firstRow;
            this.lastRow=lastRow;
            this.firstColumn=firstColumn;
            this.lastColumn=lastColumn;
            key=firstRow+","+firstColumn;
            init();
        }

        private void init(){
            for(int i=firstRow; i<=lastRow; i++){
                for(int j=firstColumn; j<=lastColumn; j++) {
                    String k=i+","+j;
                    allmerged.put(k, key);
                }
            }
        }

        public Object getValue() {
            return value;
        }

        public boolean isCol(int rinx, int cinx){
            if(firstRow<=rinx && rinx<=lastRow && firstColumn<=cinx && cinx<=lastColumn) return true;
            return false;
        }

        public void setValue(Object value) {
            this.value = value;
        }

        public String getKey() {
            return key;
        }
    }

    private Workbook wb;
    private JSONObject head;
    private JSONArray data;
    private String updateTable;
    private String mode;
    private String steetName;
    private JSONObject updateData;
    private JSONObject updateDataSp;
    private ArrayList<String> updateList;
    private int updateSize;
    private JSONObject merged;
    private XLSTable xlsTable;
    private SqlSession session;
    private XLSReadCallback xlsReadCallback;
    private int msgNo;
    private TableSaveCheck tableSaveCheck;

    public JSONArray getData() {
        return data;
    }

    public XLSTable getXlsTable() {
        return xlsTable;
    }

    public void setXlsTable(XLSTable xlsTable) {
        this.xlsTable = xlsTable;
    }

    public void setSession(SqlSession session) {
        this.session = session;
    }

    public void setXlsReadCallback(XLSReadCallback xlsReadCallback) {
        this.xlsReadCallback = xlsReadCallback;
    }

    public void exportTableToXls(String table, String file) throws Exception {
        Workbook wb= new SXSSFWorkbook(100);
        ((SXSSFWorkbook)wb).setCompressTempFiles(true);
        execSplitStr(table, ";", new SpiltStrCallback(){
            @Override
            public void exec(String id) throws Exception {
                createXls(id, wb);
            }
        });
        FileUtil.newFile(file);
        FileOutputStream out = new FileOutputStream(file);
        wb.write(out);
        out.close();
    }

    private void createXls(String tablename, Workbook wb) throws Exception {
        tablename=tablename.trim();
        Table table=null;
        if(tablename.indexOf(" ")>0){
            table=session.getSqlTable(tablename);
        } else {
            table=session.getDictTable(tablename);
        }

        String column=table.getColumn();
        String[] clist=column.split(",");
        int len=clist.length;
        String sel="";
        String shTitle=session.getTableRemark(tablename);
        if(shTitle==null) shTitle=castToString(wb.getNumberOfSheets());
        SXSSFSheet sh= (SXSSFSheet) wb.createSheet(shTitle);
        sh.createFreezePane( 0, 1, 0, 1 );
        Row row = sh.createRow(0);
        for(int i=0; i<len; i++){
            String col=clist[i];
            if(!"".equals(sel)) sel=sel+",";
            String cname=table.getRemarks(col);
            cname=specialCharacter(cname);
            int pos1=cname.indexOf("(");
            if(pos1>=0){
                cname=cname.substring(0, pos1);
            }
            sel=sel+col+" AS "+cname;
            Cell cell = row.createCell(i);
            cell.setCellValue(cname);
        }
//        String sql="SELECT "+sel+" FROM "+tablename+" WHERE 1=1 ";
//        System.out.println(sql+";");
    }

    public void readExcel(String filePath) {
        readExcel(filePath, null);
    }

    /**
     * 读取excel 第1张sheet （xls和xlsx）
     * @param filePath	excel路径
     * @return
     */
    public void readExcel(String filePath, JSONObject input) {
        if (filePath == null) {
            return;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        String file=FileUtil.getFileName(filePath);
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = null;
            }
            if (wb != null) {
                int rownum=0;
                // 用来存放表中数据
                data = new JSONArray();
                // 获取第一个sheet
                int len=wb.getNumberOfSheets();
                for(int i=0; i<len; i++){
                    msgNo=1;
                    Sheet st=wb.getSheetAt(i);
                    steetName=st.getSheetName();
                    int rlen=st.getPhysicalNumberOfRows();
                    if(xlsTable!=null){
                        if(xlsTable.getSheet(steetName)==null){
                            steetName=castToString(i);
                        }
                        mode=xlsTable.getMode(steetName);
                        updateTable=xlsTable.getUpdateTable(steetName);
                        if(updateTable==null){
                            break;
                        }
                        if(input!=null){
                            String updateTableK=xlsTable.getUpdateTableK(steetName);
                            if(updateTableK!=null){
                                updateTable=input.getString(updateTableK);
                            }
                        }
                        //if(updateTable==null) continue;
                        updateData=new JSONObject();
                        JSONObject u=new JSONObject();
                        u.putAll(xlsTable.getUpdateField(steetName));
                        ArrayList<String> uklist=u.getKeylist();
                        int ulen=uklist.size();
                        for(int z=0; z<ulen; z++){
                            if("SYS_XLS_FILE".equals(u.getString(uklist.get(z)))){
                                u.put(uklist.get(z), file);
                            }
                        }
                        updateData.putAll(u);
                        updateDataSp=new JSONObject();
                        if(updateTable!=null){
                            boolean truncate=xlsTable.isTruncate(steetName);
                            if(session!=null && truncate){
                                session.getForDDL().truncateTable(updateTable);
                            }
                            tableSaveCheck=new TableSaveCheck(session, updateTable);
                            tableSaveCheck.setHead(xlsTable.getHead(steetName));
                        }
                        ArrayList<String> klist=updateData.getKeylist();
                        int klen=klist.size();
                        for(int k=0; k<klen; k++){
                            String v=updateData.getString(klist.get(k));
                            if("{".equals(v.substring(0,1))){
                                String key=klist.get(k);
                                updateDataSp.put(key, updateData.get(key));
                                updateData.remove(key);
                            }
                        }
                        updateList=updateDataSp.getKeylist();
                        updateSize=updateList.size();
                    }
                    head=new JSONObject();
                    merged=new JSONObject();
                    getMerged(st);
                    for(int r=0; r<rlen; r++){
                        if(r==0){
                            rownum=setHeadRow(st.getRow(r));
                        } else {
                            setRow(rownum, r, st.getRow(r));
                        }
                    }
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private int setHeadRow(Row row) throws Exception {
        int len=row.getLastCellNum();
        for(int i=0; i<len; i++){
            Cell cell=row.getCell(i);
            String k=null;
            try{
                k=cell.getStringCellValue();
                if(k!=null){
                    k=k.trim();
                }
            } catch (Exception e){
            }
            if(k==null){
                continue;
            }
            if(xlsTable!=null && xlsTable.getHead(steetName).get(k)!=null){
                k=xlsTable.getHead(steetName).getString(k);
            }
            head.put(castToString(i), k);
        }
        if(xlsReadCallback!=null){
            head=xlsReadCallback.getHead(head, steetName);
        }
        return len;
    }

    private void setRow(int len, int rinx, Row row) throws Exception {
        if(len==0) return;
        JSONObject map=new JSONObject();
        Table utable=session.getDictTable(updateTable);
        map.put("R_ID", rinx);
        for(int i=0; i<len; i++){
            String k=head.getString(castToString(i));
            Cell cell=row.getCell(i);
            Object value=null;
            if(cell!=null){
                value=getCellValue(cell);
                String key=rinx+","+i;
                String key1=allmerged.getString(key);
                if(key1!=null){
                    if(value!=null){
                        Merged m= (Merged) merged.get(key1);
                        if(m!=null) m.setValue(value);
                        merged.put(key, m);
                    } else value=getMergedValue(rinx, i, cell);
                }
                if(cell.getHyperlink()!=null){
                    map.put(k+".LINK", cell.getHyperlink().getAddress());
                }
            }
            if(value!=null){
                if(xlsTable!=null){
                    String look=xlsTable.getLook(steetName).getString(k);
                    String key=k;
                    if(look!=null){
                        key=look;
                    }
                    XLSTable.Field field= (XLSTable.Field) xlsTable.getField(steetName).get(key);
                    if(field!=null){
                        if(field.getListmap()!=null) {
                            value=field.getListmap().get(value);
                        }
                        if(field.getTable()!=null && session!=null){
                            JSONArray list=session.getForJSON().query(" SELECT "+field.getField()+" FROM "+field.getTable()+" WHERE "+field.getLook()+"='"+value+"'");
                            if(list!=null && list.size()>0){
                                map.put(key, list.get(0).get(look));
                            }
                        }
                    }
                    if(xlsTable.getIdHead(steetName).get(k)==null){
                        value=null;
                    }
                }
                if(value!=null){
                    if(utable!=null){
                        String type=utable.getDbType(k);
                        if(type.indexOf("CHAR")>=0 || (type.indexOf("NUMBER")>=0 && utable.getSize(k)==0)){
                            if(value instanceof  Double){
                                value=castToInt(value);
                                value=castToString(value);
                            }
                        }
                    }
                    map.put(k, value);
                }
            }
        }
        if(map.size()>0){
            //System.out.println(JSON.toJSONString(map));
            if(xlsTable!=null && session!=null){
                JSONObject up=new JSONObject();
                up.putAll(map);
                up.putAll(updateData);
                for(int i=0; i<updateSize; i++){
                    String key=updateList.get(i);
                    String v=updateDataSp.getString(key);
                    if("{ROWID}".equals(v)){
                        up.put(key, rinx+1);
                    } else if("{TIMESTAMP_STR}".equals(v)){
                        up.put(key, DateUtil.getTimeStampStr(new Date()));
                    }
                }
                if(xlsReadCallback!=null){
                    up=xlsReadCallback.getRow(up, rinx);
                }
                if(updateTable!=null){
                    try{
                        tableSaveCheck.checkBaseData(up);
                        if(mode==null){
                            session.insertTable_T(updateTable, up);
                        } else if("save".equals(mode)){
                            session.saveTable_T(updateTable, up);
                        }
                    } catch (Exception e){
                        writeError(e, up);
                    }
                }
            }
        }
    }

    private void writeError(Exception e, JSONObject up) throws Exception {
        if(updateTable!=null){
            String msg=e.getMessage();
            if(msg==null || "".equals(msg)) msg=e.toString();
            JSONObject m=new JSONObject();
            m.putAll(up);
            if(tableSaveCheck!=null){
                String s=tableSaveCheck.getErrorMsg();
                if(!"".equals(s) && !"[]".equals(s)){
                    msg=s;
                    ArrayList<TableSaveCheck.ErrorField> list=tableSaveCheck.getDfieldlist();
                    int flen=list.size();
                    for(int i=0; i<flen; i++){
                        m.remove(list.get(i).getId());
                    }
                    list=tableSaveCheck.getMfieldlist();
                    flen=list.size();
                    for(int i=0; i<flen; i++){
                        m.remove(list.get(i).getId());
                    }
                    list=tableSaveCheck.getIfieldlist();
                    flen=list.size();
                    for(int i=0; i<flen; i++){
                        m.remove(list.get(i).getId());
                    }
                    if(msg!=null){
                        m.put("IMP_MSG", msg);
                        m.put("IMP_STATE",msgNo);
                        session.insertTable_T(updateTable, m);
                        msgNo++;
                    }
                } else {
                    boolean flag=false;
                    while(!flag){
                        try{
                            msg=tableSaveCheck.getDBMsg(msg, m);
                            if(msg==null){
                                msg="["+m.getString("index_error")+"]没有找到对应的主数据，请补全！";
                            }
                            m.put("IMP_MSG", msg);
                            m.put("IMP_STATE",msgNo);
                            session.insertTable_T(updateTable, m);
                            flag=true;
                        } catch (Exception e1){
                            flag=false;
                            msg=e1.getMessage();
                        }
                        msgNo++;
                    }
                }
            }
        } else {
            throw e;
        }
    }

    private Object getCellValue(Cell cell){
        Object value=null;
        if(cell.getCellType() == Cell.CELL_TYPE_STRING){   //字符串
            value=cell.getStringCellValue();
            if(value!=null){
                value=((String) value).trim();
            }
        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){   //Boolean
            value=cell.getBooleanCellValue();
        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){   //公式
            value=cell.getCellFormula();
        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){   //数字
            value=cell.getNumericCellValue();
            if(HSSFDateUtil.isCellDateFormatted(cell)){
                Date d=org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Double) value);
                value=d;
            }
        }else if(cell.getCellType() == Cell.CELL_TYPE_ERROR){   //故障
            value="非法字符";
        }
        return value;
    }


    private void getMerged(Sheet sheet) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            Merged m=new Merged(firstRow, lastRow, firstColumn, lastColumn);
            merged.put(m.getKey(),m);
        }
    }

    private Object getMergedValue(int rinx, int cinx, Cell cell) {
        String key=rinx+","+cinx;
        String k=allmerged.getString(key);
        Merged m= (Merged) merged.get(k);
        if(m==null) return null;
        Object val=m.getValue();
        if(val!=null && val instanceof String){
            val=((String) val).trim();
        }
        return val;
    }

}
